import openpyxl
from config import users_list, exclusion, project

def new_data(sheet_index):
    # print(wb.sheetnames)
    ws = wb.worksheets[sheet_index]
    nrows = ws.max_row
    ncols = ws.max_column
    # print(nrows, ncols)

    # 获取配置文件config中exclusion，并将clientid组成exclusion_list
    exclusion_list = []
    for i in range(len(exclusion)):
        exclusion_list.append(exclusion[i].get('clientid'))
    # print(exclusion_list)

    # print(ws.merged_cells.ranges)
    # 计算出合并的单元格
    colspan = {}
    if ws.merged_cells:
        for merge in ws.merged_cells:
            # print(merge)
            for row in range(merge.min_row, merge.max_row+1):
                for col in range(merge.min_col, merge.max_col+1):
                    colspan.update({(row, col): (merge.min_row, merge.max_col)})
    # print(colspan)

    # 读取每行数据
    all_new_data = []
    for row in range(2, nrows+1):
        row_dict = {}
        row_list = []
        for col in range(1, 4):
            # 假如遇到合并的单元格坐标，取合并的首格的值即可
            if colspan.get((row, col)):
                row_list.append(ws.cell(*colspan.get((row, col))).value)
            else:
                row_list.append(ws.cell(row, col).value)
        # print(row_list)
        row_dict['api'] = row_list
        row_dict['count'] = ws.cell(row, ncols).value
        # print('row_dict', row_dict)
        all_new_data.append(row_dict)
        # if row_dict['api'] not in unique_data:
        #     # row_dict['api'].append(row_dict['count'])
        #     unique_data.append(row_dict['api'])
    # print('all_new_data', len(all_new_data), all_new_data)

    # 将每行数据格式化为history数据类型
    all_new_data_1 = []
    for row in all_new_data:
        dict_1 = row
        other_list = [None, None]
        clientid = row['api'][1]
        clientid_list = str(clientid).split(',')

        # 根据clientid数据，获取对应的project
        project_link = ''
        for i in clientid_list:
            for j in range(len(project)):
                if i == project[j]['clientid']:
                    if project_link == '':
                        project_link = project[j]['project']
                    else:
                        project_link = project_link + '-->' + project[j]['project']
        other_list.append(project_link)

        # 先排除exclusion_list中无需确认的clientid，然后根据剩余clientid确认'负责人','部门','主负责人'
        new_clientid_list = []
        for i in clientid_list:
            if i not in exclusion_list:
                new_clientid_list.append(i)
        new_clientid_list.reverse()
        for i in range(len(users_list)):
            if new_clientid_list[0] in users_list[i].get('clientid'):
                other_list.extend([users_list[i].get('username'), None, None, users_list[i].get('department'), users_list[i].get('owner'), None])
        dict_1['other'] = other_list
        dict_1['new_count'] = None
        dict_1['state'] = '新发现'
        all_new_data_1.append(dict_1)

    return all_new_data_1



def history_data(sheet_index):
    # print(wb.sheetnames)
    ws = wb.worksheets[sheet_index]
    nrows = ws.max_row
    ncols = ws.max_column
    # print(nrows, ncols)

    # 读取每行数据
    all_history_data = []

    for row in range(2, nrows+1):
        row_dict = {}
        api_list = []
        for col in range(1, 4):
            api_list.append(ws.cell(row, col).value)
        count = ws.cell(row, 4).value
        other_list = []
        for col in range(5, ncols-1):
            other_list.append(ws.cell(row, col).value)
        new_count = ws.cell(row, ncols-1).value
        state = ws.cell(row, ncols).value
        row_dict['api'] = api_list
        row_dict['count'] = count
        row_dict['other'] = other_list
        row_dict['new_count'] = new_count
        row_dict['state'] = '存量'

        all_history_data.append(row_dict)

    #     print('row_dict', row_dict)
    # print('all_history_data', len(all_history_data), all_history_data)
    return all_history_data

def unique_data(history_index, new_index):
    print('*****************')
    history_data_1 = []
    new_data_1 = new_data(new_index)
    for i in history_data(history_index):
        for j in new_data(new_index):
            if j['api'] == i['api']:
                new_data_1.remove(j)
                if i['other'][1] == None:
                    pass
                elif j['count'] > i['other'][1]:
                    i['new_count'] = j['count']
        # print('new_i', i)
        history_data_1.append(i)
        all_date = history_data_1 + new_data_1
    print('new_data_1', len(new_data_1), new_data_1)
    print('history_data_1', len(history_data_1), history_data_1)
    print('all_data', len(all_date), all_date)

    summary_sheet = wb.create_sheet('new_summary')
    summary_sheet.append(
        ['servletPath', 'clientId', 'initiateUrl', 'count', '说明超过1万次调用的原因', '阈值上限', 'project', '负责人', '描述', '是否合理',
         '部门', '主负责人', '说明超过阈值的原因', 'new_count', '存量/新发现'])
    for i in all_date:
        all_date_list = []
        for j in i.values():
            if isinstance(j, list):
                all_date_list.append(j)
            else:
                all_date_list.append([j])

        all_data_list_1 =[]
        for k in all_date_list:
            all_data_list_1 += k
        # print('all_data_list_1', len(all_data_list_1), all_data_list_1)

        # 写入每列数据到新建的sheet中
        summary_sheet.append(all_data_list_1)
        # summary_sheet.freeze_panes = 'A2'
    wb.save(file)


if __name__ == '__main__':
    file = '2021-5-25.xlsx'
    wb = openpyxl.load_workbook(file)
    # new_data(1)
    # history_data(0)
    unique_data(0, 1)
